﻿
'*******************************************************************
' Program: Data Center Visio Rack Tool
' Author: Albert E Edlund
' Date:
'
' Purpose:
'
'
'*******************************************************************

Imports Microsoft.Practices.EnterpriseLibrary.Common
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports Microsoft.Practices.EnterpriseLibrary.ExceptionHandling

Imports System
Imports System.ComponentModel
Imports System.Collections.Generic

Imports System.Collections.ObjectModel


Imports System.Data
Imports System.IO
Imports System.Xml
Imports System.Collections
Imports System.Runtime.InteropServices


Imports DC_Rack.Objects.clsDC_RackData


Public Class clsPortMaster

#Region " Properties and declarations "


    Public m_db As Database
    Public m_ds As DataSet
    Public m_dt As DataTable

    Public m_dc As dc_PortBase
    Public m_oc As Collection(Of dc_PortBase)



    Public m_deleteCommand As DbCommand
    Public m_insertCommand As DbCommand
    Public m_updateCommand As DbCommand


    Public Const readAll As String = "selectTblPortMasterData"

    ' who am I
    Private myEnum As DC_RackTables = DC_RackTables.tblPortMasterData

    Private m_parentform As Object
    Property _ParentForm() As Object
        Get
            Return Me.m_parentform
        End Get
        Set(ByVal value As Object)
            Me.m_parentform = value
        End Set
    End Property



#End Region

#Region " move/transform data "


    '
    ' create columns
    '
    Public Sub CreateTableColumns(ByRef tempTable As DataTable)

        Dim newDC As dc_PortBase = New dc_PortBase

        Try
            Dim colPortProductKey As New System.Data.DataColumn
            colPortProductKey.ColumnName = "portProductKey"
            colPortProductKey.DataType = GetType(Integer)
            colPortProductKey.DefaultValue = newDC.constPortProductKey
            tempTable.Columns.Add(colPortProductKey)

            Dim colPortName As New System.Data.DataColumn
            colPortName.ColumnName = "portName"
            colPortName.DataType = GetType(String)
            colPortName.DefaultValue = newDC.constPortName
            tempTable.Columns.Add(colPortName)

            Dim colPortProductView As New System.Data.DataColumn
            colPortProductView.ColumnName = "portProductView"
            colPortProductView.DataType = GetType(Int16)
            colPortProductView.DefaultValue = newDC.constPortProductView
            tempTable.Columns.Add(colPortProductView)

            Dim colPortTechnology As New System.Data.DataColumn
            colPortTechnology.ColumnName = "portTechnology"
            colPortTechnology.DataType = GetType(Integer)
            colPortTechnology.DefaultValue = newDC.constPortTechnology
            tempTable.Columns.Add(colPortTechnology)

            Dim colPortConnector As New System.Data.DataColumn
            colPortConnector.ColumnName = "portConnector"
            colPortConnector.DataType = GetType(Int16)
            colPortConnector.DefaultValue = newDC.constPortConnector
            tempTable.Columns.Add(colPortConnector)

            Dim colPortTraffic As New System.Data.DataColumn
            colPortTraffic.ColumnName = "portTraffic"
            colPortTraffic.DataType = GetType(Int16)
            colPortTraffic.DefaultValue = newDC.constPortTraffic
            tempTable.Columns.Add(colPortTraffic)

            Dim colPortWellFormedName As New System.Data.DataColumn
            colPortWellFormedName.ColumnName = "portWellFormedName"
            colPortWellFormedName.DataType = GetType(Boolean)
            colPortWellFormedName.DefaultValue = newDC.constPortWellFormedName
            tempTable.Columns.Add(colPortWellFormedName)

            Dim colPortBuildDate As New System.Data.DataColumn
            colPortBuildDate.ColumnName = "portBuildDate"
            colPortBuildDate.DataType = GetType(Date)
            colPortBuildDate.DefaultValue = newDC.constPortBuildDate
            tempTable.Columns.Add(colPortBuildDate)

            newDC = Nothing

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try
    End Sub


    '
    ' use for writing records
    '
    Public Sub movePropertiesToDataRow _
            (ByRef tempRow As DataRow, _
             ByVal tempDC As dc_PortBase)

        Try

            With tempDC
                tempRow.SetField("portProductKey", .portProductKey)
                tempRow.SetField("portProductView", .portProductView)
                tempRow.SetField("portName", .portName)
                tempRow.SetField("portTechnology", .portTechnology)
                tempRow.SetField("portConnector", .portConnector)
                tempRow.SetField("portTraffic", .portTraffic)
                tempRow.SetField("portWellFormedName", .portWellFormedName)
                tempRow.SetField("portBuildDate", .portBuildDate)
            End With


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    ' for reading records
    '
    Public Sub moveDataRowToProperties _
            (ByVal tempRow As DataRow)

        Try

            With m_dc
                .initProperties()
                .portProductKey = tempRow.Item("portProductKey")
                .portProductView = tempRow.Item("portProductView")
                .portName = tempRow.Item("portName")
                .portTechnology = tempRow.Item("portTechnology")
                .portConnector = tempRow.Item("portConnector")
                .portTraffic = tempRow.Item("portTraffic")
                .portWellFormedName = tempRow.Item("portWellFormedName")
                .portBuildDate = tempRow.Item("portBuildDate")
            End With


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    ' for reading records
    '
    Public Sub moveDataRowToProperties _
            (ByVal tempRow As DataRow, _
             ByRef tempDC As dc_PortBase)

        Try

            With tempDC
                .initProperties()
                .portProductKey = tempRow.Item("portProductKey")
                .portProductView = tempRow.Item("portProductView")
                .portName = tempRow.Item("portName")
                .portTechnology = tempRow.Item("portTechnology")
                .portConnector = tempRow.Item("portConnector")
                .portTraffic = tempRow.Item("portTraffic")
                .portWellFormedName = tempRow.Item("portWellFormedName")
                .portBuildDate = tempRow.Item("portBuildDate")
            End With


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


    '
    ' for moving active table to collection for wpf
    '
    Public Sub moveDataTableToObservableCollection _
            (ByVal dtTemp As DataTable)

        Try

            If 0 < dtTemp.Rows.Count Then

                If m_oc IsNot Nothing Then
                    m_oc = Nothing
                    m_oc = New Collection(Of dc_PortBase)
                Else
                    m_oc = New Collection(Of dc_PortBase)
                End If

                Dim drTemp As DataRow
                Dim dcTemp As dc_PortBase
                For Each drTemp In dtTemp.Rows
                    dcTemp = New dc_PortBase
                    moveDataRowToProperties(drTemp, dcTemp)
                    m_oc.Add(dcTemp)
                Next

            End If

        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try



    End Sub

    '
    '
    '
    Public Function moveDataTableToXML() As XmlDocument

        Dim writer = New System.IO.StringWriter
        Dim m_dt_xml As Xml.XmlDocument = Nothing

        Try


            m_dt.WriteXml(writer, XmlWriteMode.WriteSchema, True)
            m_dt_xml = Nothing
            m_dt_xml = New Xml.XmlDocument
            m_dt_xml.LoadXml(writer.ToString)

        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Finally

            writer = Nothing

        End Try

        Return m_dt_xml


    End Function


#End Region

#Region " standard database calls "

    '
    '
    '
    Private Sub buildInsertPortMasterCommand()


        Try

            m_insertCommand = m_db.GetStoredProcCommand("insertPortMaster")
            Dim cmdActive As DbCommand = m_insertCommand

            m_db.AddInParameter(cmdActive, "portProductKey", DbType.Int32, "portProductKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "portProductView", DbType.String, "portProductView", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "portName", DbType.String, "portName", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "portTechnology", DbType.Int16, "portTechnology", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "portConnector", DbType.String, "portConnector", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "portTraffic", DbType.Int16, "portTraffic", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "portBuildDate", DbType.Date, "portBuildDate", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "portWellFormedName", DbType.Boolean, "portWellFormedName", DataRowVersion.Current)

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try



    End Sub

    '
    '
    '
    Private Sub buildUpdatePortMasterCommand()



        Try

            m_updateCommand = m_db.GetStoredProcCommand("updatePortMaster")
            Dim cmdActive As DbCommand = m_updateCommand

            m_db.AddInParameter(cmdActive, "portProductKey", DbType.Int32, "portProductKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "portProductView", DbType.Int16, "portProductView", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "portName", DbType.String, "portName", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "portTechnology", DbType.Int16, "portTechnology", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "portConnector", DbType.Int16, "portConnector", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "portTraffic", DbType.Int16, "portTraffic", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "portBuildDate", DbType.Date, "portBuildDate", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "portWellFormedName", DbType.Boolean, "portWellFormedName", DataRowVersion.Current)

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try



    End Sub

    '
    '
    '
    Private Sub buildDeletePortMasterCommand()


        Try
            m_deleteCommand = m_db.GetStoredProcCommand("deletePortMaster")
            Dim cmdActive As DbCommand = m_deleteCommand

            m_db.AddInParameter(cmdActive, "portProductKey", DbType.Int32, "portProductKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "portProductView", DbType.Int16, "portProductView", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "portName", DbType.String, "portName", DataRowVersion.Current)

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    '
    '
    Public Function dbUpdateDataSet() As Int16

        Dim rowsAffected As Int16 = 0

        Try

            rowsAffected = m_db.UpdateDataSet(m_ds, dc_racktables.tblportmasterdata.tostring, _
                                        m_insertCommand, _
                                        m_updateCommand, _
                                        m_deleteCommand, _
                                        UpdateBehavior.Standard)
            'msgbox rowsAffected

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If

        End Try

        Return rowsAffected

    End Function

    '
    ' create a record based on property values move it to the generic datatable
    ' then copy the table to the database
    '
    Public Function addPortMasterDataSourceRecord _
            (ByVal tempDC As dc_PortBase, _
             Optional ByVal blnSilent As Boolean = False) _
             As Integer

        Dim intRowsAffected As Integer = 0

        Try

            initDataSetAndTable()

            Dim m_dr As DataRow = m_ds.Tables(dc_racktables.tblportmasterdata.tostring).NewRow
            movePropertiesToDataRow(m_dr, tempDC)
            m_ds.Tables(dc_racktables.tblportmasterdata.tostring).Rows.Add(m_dr)

            intRowsAffected = dbUpdateDataSet()

            If blnSilent = False Then
                MsgBox("DataRowsUpated = " & intRowsAffected)
            End If

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        Return intRowsAffected

    End Function

    '
    ' clear the general dataset and table, create columns in the table, and move it to
    ' the dataset
    '
    Public Sub initDataSetAndTable()

        Try

            ' make sure the root stuff is there
            If m_ds Is Nothing Then
                m_ds = New DataSet
            Else
                m_ds = Nothing
                m_ds = New DataSet
            End If
            m_ds.SchemaSerializationMode = SchemaSerializationMode.IncludeSchema

            If m_dt Is Nothing Then
                m_dt = New DataTable
            Else
                m_dt = Nothing
                m_dt = New DataTable
            End If

            If m_oc Is Nothing Then
                m_oc = New Collection(Of dc_PortBase)
            Else
                m_oc = Nothing
                m_oc = New Collection(Of dc_PortBase)
            End If

            If m_dc Is Nothing Then
                m_dc = New dc_PortBase
            Else
                m_dc = Nothing
                m_dc = New dc_PortBase
            End If

            CreateTableColumns(m_dt)
            m_dt.TableName = dc_racktables.tblportmasterdata.tostring

            m_ds.Tables.Add(m_dt)


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try



    End Sub




#End Region

#Region " database calls "

    '
    ' clear the table of all items
    '
    Public Sub dbDeletePortMasters()


        Try

            Dim cmd As DbCommand = m_db.GetStoredProcCommand("deleteTblPortMasterData")
            m_db.ExecuteNonQuery(cmd)

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    '
    '
    Public Sub dbInsertPortMaster _
        (ByVal PortMaster As dc_PortBase)

        Dim strInsert As String = ""
        Dim strParm As String = ""
        Dim m_sql As String = ""

        Try

            With PortMaster


                strInsert = "INSERT INTO tblPortMasterData " _
                    & "(portProductKey,portProductView, portName, " _
                    & "portTechnology, portConnector, wellformedname, " _
                    & "portbuilddate" _
                    & ") "

                strParm = "Values ( " _
                    & .portProductKey & ", """ & .portProductView & """, """ & .portName & """, " _
                    & .portTechnology & ", """ & .portConnector & """, " & .portWellFormedName & ", " _
                    & "#" & Now & "#" _
                    & ");"
                m_sql = strInsert & strParm

                Dim cmd As DbCommand = m_db.GetSqlStringCommand(m_sql)

                m_db.ExecuteNonQuery(cmd)

            End With

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    '
    '
    Public Sub dbInsertPortMasterCollection()


        Try
            Dim intX As Integer
            Dim intCt As Integer
            intCt = -1
            Dim clsPort As dc_PortBase = New dc_PortBase

            If m_oc Is Nothing Then Exit Sub
            intCt = m_oc.Count
            If intCt < 1 Then Exit Sub


            If m_ds Is Nothing Then
                m_ds = New DataSet
            Else
                m_ds = Nothing
                m_ds = New DataSet
            End If
            m_ds.SchemaSerializationMode = SchemaSerializationMode.IncludeSchema
            If m_dt Is Nothing Then
                m_dt = New DataTable
            Else
                m_dt = Nothing
                m_dt = New DataTable
            End If

            ' Retrieve the initial data
            Dim dbCommand As DbCommand
            dbCommand = m_db.GetStoredProcCommand(readAll)
            m_db.LoadDataSet(dbCommand, m_ds, dc_racktables.tblportmasterdata.tostring)

            m_dt = m_ds.Tables(dc_racktables.tblportmasterdata.tostring)

            Dim addedRow As DataRow

            Dim returnValue As String()

            For intX = 0 To intCt - 1
                clsPort = m_oc.Item(intX)
                With clsPort

                    returnValue = .portName.Split({"_"c}, _
                        3, StringSplitOptions.None)

                    addedRow = m_dt.Rows.Add(New Object() _
                                {.portProductKey, _
                                .portProductView, _
                                .portName, _
                                CInt(returnValue(2)), _
                                .portConnector, _
                                .portTraffic, _
                                .portWellFormedName, _
                                Now()})
                End With
            Next intX

            Dim rowsAffected As Int16
            rowsAffected = dbUpdateDataSet()
            'msgbox rows affected

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    '
    '
    Public Sub dbSelectAllPortMasters()

        Try

            initDataSetAndTable()

            ' Retrieve the initial data
            Dim cmd As DbCommand = m_db.GetStoredProcCommand(readAll)
            m_db.LoadDataSet(cmd, m_ds, dc_racktables.tblportmasterdata.tostring)

            m_dt = m_ds.Tables(dc_racktables.tblportmasterdata.tostring)


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

#End Region

#Region " Constructor "



    Public Sub New()

        Try


            m_db = DatabaseFactory.CreateDatabase()
            m_ds = New DataSet
            m_dt = New DataTable

            initDataSetAndTable()

            m_updateCommand = Nothing
            m_insertCommand = Nothing
            m_deleteCommand = Nothing


            buildDeletePortMasterCommand()
            buildInsertPortMasterCommand()
            buildUpdatePortMasterCommand()



        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


#End Region


End Class
